package com.presario.robot;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入配置表
 */

public class ReadNewTemplate {

    static final String CHANNEL_DECISION = "insert into channel_decision_new (channel_code,decision_code,business_source) values('%s','%s','%s');";
    static final String CHANNEL_AGREEMENT = "insert into channel_agreement_new (channel_code,agreement_code,agreement_name) values('%s','%s','%s');";
    static final String CHANNEL_ORG = "insert into channel_org_new (channel_code,org_code,org_name) values('%s','%s','%s');";
    static final String CHANNEL_HANDLER = "insert into channel_handler_new (channel_code,handler_code,handler_name) values('%s','%s','%s');";
    static final String CONFIG_USER = "insert into config_user_new (org_code,user_name,nick_name,channel_code) values('%s','%s','%s','%s');";


    public static void main(String[] args) {
        //read("E:\\work\\east\\pengyong\\国任保险\\生产环境\\新模板-AI配置导入模板_20250530.xlsx");
        read("E:\\work\\east\\pengyong\\国任保险\\生产环境\\AI配置导入模板-河南.xlsx");
    }

    public static void read(String excelFilePath) {
        StringBuilder channelDecisions = new StringBuilder();
        StringBuilder channelAgreements = new StringBuilder();
        StringBuilder channelOrgs = new StringBuilder();
        StringBuilder channelHandlers = new StringBuilder();
        StringBuilder userConfigs = new StringBuilder();

        Map<String, List<String>> channelAgreementMap = new HashMap<>();
        Map<String, List<String>> channelOrgMap = new HashMap<>();
        Map<String, List<String>> channelHandlerMap = new HashMap<>();
        Map<String, List<String>> channelDecisionMap = new HashMap<>();

        List<String> userConfigsList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
            Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            int index = 0;
            for (Row row : sheet) {
                index++;
                if (index > 1) {

                    // 渠道代码
                    String channelCode = getCellValueAsString(row.getCell(0));

                    // 归属机构
                    String orgCode = getCellValueAsString(row.getCell(3));
                    String orgName = getCellValueAsString(row.getCell(4));

                    // 业务来源
                    String businessSource = getCellValueAsString(row.getCell(6));

                    //  归属业务员
                    String handlerCode = getCellValueAsString(row.getCell(10));
                    String handlerName = getCellValueAsString(row.getCell(11));

                    // 协议名称
                    String agreementName = getCellValueAsString(row.getCell(8));
                    // 协议编号
                    String agreementCode = getCellValueAsString(row.getCell(9));

                    // 决策单元代码
                    String decisionCode = getCellValueAsString(row.getCell(18));

                    // 行政机构
                    String firstOrgCode = getCellValueAsString(row.getCell(1));
                    // 出单人员名称
                    String userName = getCellValueAsString(row.getCell(17));
                    // 个人微信昵称
                    String nickName = getCellValueAsString(row.getCell(24));

                    if (StringUtil.isNotBlank(nickName) && !userConfigsList.contains(firstOrgCode+userName+nickName)) {
                        String userConfig = String.format(CONFIG_USER, firstOrgCode, userName, nickName, channelCode);
                        userConfigsList.add(firstOrgCode+userName+nickName);
                        userConfigs.append(userConfig).append("\n");
                    }


                    if (StringUtil.isNotBlank(decisionCode)) {
                        List<String> decisionCodes = channelDecisionMap.get(channelCode);
                        if (decisionCodes == null) {
                            decisionCodes = new ArrayList<>();
                        }
                        if (!decisionCodes.contains(decisionCode)) {
                            decisionCodes.add(decisionCode);
                            String channelDecision = String.format(CHANNEL_DECISION, channelCode, decisionCode, businessSource);
                            channelDecisions.append(channelDecision).append("\n");
                        }
                        channelDecisionMap.put(channelCode, decisionCodes);
                    }

                    if (StringUtil.isNotBlank(orgCode)) {
                        List<String> orgCodes = channelOrgMap.get(channelCode);
                        if (orgCodes == null) {
                            orgCodes = new ArrayList<>();
                        }
                        if (!orgCodes.contains(orgCode)) {
                            orgCodes.add(orgCode);
                            String channelOrg = String.format(CHANNEL_ORG, channelCode, orgCode, orgName);
                            channelOrgs.append(channelOrg).append("\n");
                        }
                        channelOrgMap.put(channelCode, orgCodes);
                    }

                    if (StringUtil.isNotBlank(handlerCode)) {
                        List<String> handlerCodes = channelHandlerMap.get(channelCode);
                        if (handlerCodes == null) {
                            handlerCodes = new ArrayList<>();
                        }
                        if (!handlerCodes.contains(handlerCode)) {
                            handlerCodes.add(handlerCode);
                            String channelHandler = String.format(CHANNEL_HANDLER, channelCode, handlerCode, handlerName);
                            channelHandlers.append(channelHandler).append("\n");
                        }
                        channelHandlerMap.put(channelCode, handlerCodes);
                    }

                    if (StringUtil.isNotBlank(agreementCode)) {
                        List<String> agreementCodes = channelAgreementMap.get(channelCode);
                        if (agreementCodes == null) {
                            agreementCodes = new ArrayList<>();
                        }
                        if (!agreementCodes.contains(agreementCode)) {
                            agreementCodes.add(agreementCode);
                            agreementName = agreementName.replace("\n", "");
                            String channelAgreement = String.format(CHANNEL_AGREEMENT, channelCode, agreementCode, agreementName);
                            channelAgreements.append(channelAgreement).append("\n");
                        }
                        channelAgreementMap.put(channelCode, agreementCodes);
                    }
                }
            }

            channelDecisions.append("\n");
            channelAgreements.append("\n");
            channelOrgs.append("\n");
            channelHandlers.append("\n");

            java.text.SimpleDateFormat sdf  = new java.text.SimpleDateFormat("yyyyMMdd");

            String today = sdf.format(Calendar.getInstance().getTime());
            String output = "E:\\temp\\guoren\\" + today + "\\";
            File file =  new File(output);
             if (!file.exists()) {
                file.mkdirs();
            }


            write(output + "channelDecisions.txt", channelDecisions.toString());
            write(output + "channelAgreements.txt", channelAgreements.toString());
            write(output + "channelOrgs.txt", channelOrgs.toString());
            write(output + "channelHandlers.txt", channelHandlers.toString());
            write(output + "userConfigs.txt", userConfigs.toString());

            String channelDesionAppend = "insert into \n" +
                    "channel_decision\n" +
                    "(channel_code,\n" +
                    "decision_code,\n" +
                    "business_source\n" +
                    ")\n" +
                    "select channel_code,\n" +
                    "decision_code,\n" +
                    "business_source\n" +
                    "from channel_decision_new \n" +
                    "where (channel_code,decision_code) \n" +
                    "not in \n" +
                    "(select \n" +
                    "channel_code,decision_code\n" +
                    "from channel_decision\n" +
                    ");";

            String channelHandlerAppend = "insert into \n" +
                    "channel_handler\n" +
                    "(channel_code,handler_code,handler_name)\n" +
                    "select channel_code,handler_code,handler_name\n" +
                    "from channel_handler_new \n" +
                    "where (channel_code,handler_code) \n" +
                    "not in \n" +
                    "(select \n" +
                    "channel_code,handler_code\n" +
                    "from channel_handler\n" +
                    ");";

            String channelOrgAppend = "insert into \n" +
                    "channel_org\n" +
                    "(channel_code,org_code,org_name)\n" +
                    "select channel_code,org_code,org_name\n" +
                    "from channel_org_new \n" +
                    "where (channel_code,org_code) \n" +
                    "not in \n" +
                    "(select \n" +
                    "channel_code,org_code\n" +
                    "from channel_org\n" +
                    ");" ;

             String channelAgreementAppend = "insert into \n" +
                     "channel_agreement\n" +
                     "(channel_code,agreement_code,agreement_name)\n" +
                     "select channel_code,agreement_code,agreement_name\n" +
                     "from channel_agreement_new \n" +
                     "where (channel_code,agreement_code) \n" +
                     "not in \n" +
                     "(select \n" +
                     "channel_code,agreement_code\n" +
                     "from channel_agreement\n" +
                     ");";


             String userConfigAppend = "insert into config_user\n" +
                     "(org_code,user_name,nick_name,channel_code)\n" +
                     "select \n" +
                     "org_code,user_name,nick_name,channel_code\n" +
                     "from config_user_new  \n" +
                     "where (org_code,user_name,channel_code)\n" +
                     "not in \n" +
                     "(\n" +
                     "select \n" +
                     "org_code,user_name,channel_code\n" +
                     "from config_user \n" +
                     ");" ;

             StringBuilder dataAppends  = new StringBuilder();
             dataAppends.append(channelDesionAppend).append("\n");
             dataAppends.append(channelHandlerAppend).append("\n");
             dataAppends.append(channelOrgAppend).append("\n");
             dataAppends.append(channelAgreementAppend).append("\n");
             dataAppends.append(userConfigAppend).append("\n");

             write(output + "dataAppend.txt", dataAppends.toString());


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void write(String fileName, String content) {
        try (FileWriter fileWriter = new FileWriter(fileName)) {
            fileWriter.write(content);
            System.out.println("write " + fileName + " success");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString().trim();
                } else {
                    // 使用 DecimalFormat 避免科学计数法
                    DecimalFormat df = new DecimalFormat("#");
                    return df.format(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue()).trim();
            case FORMULA:
                return cell.getCellFormula().trim();
            default:
                return "";
        }
    }
}
